package com.swn.common.base;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.swn.common.util.PageListData;

public class BaseDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setNamedParameterJdbcTemplate(
			NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	/**
	 * 公共分页方法
	 * 
	 * @param page
	 * @param sql
	 * @return
	 * @throws Exception List<Map<String,Object>>
	 *
	 * @Author Administrator
	 * @Date 2016-5-4 上午10:06:25 
	 * @Note <这里是方法的备注>
	 * 修改日期  修改人  修改内容
	 */
	public List<Map<String, Object>> queryPageMapList(PageListData page,String sql) throws Exception {
		String paginationSql = createPageSql(sql, page);
		return this.getJdbcTemplate().queryForList(paginationSql);
	}

	public int execSql(String sql) {
		return jdbcTemplate.update(sql);
	}

	public int[] batchExecSql(String[] sql) {
		return jdbcTemplate.batchUpdate(sql);
	}

	public int querySeqNextVal(String seqName) {
		String sql = "select " + seqName + "_seq.nextval from dual";
		Long a = this.getJdbcTemplate().queryForObject(sql, Long.class);
		return a.intValue();
	}

	/*
	 * 根据sql查询一个字符串类型的属性
	 */
	public String queryForOneStrProperty(String sql) {
		return (String) jdbcTemplate.queryForObject(sql, String.class);
	}

	/*
	 * 转化为分页sql（内包含查询总条数并给page赋值）
	 */
	public String createPageSql(String sql, PageListData page)
			throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		if (null == page) {
			throw new IllegalArgumentException();
		}
		StringBuffer sqlStr = new StringBuffer("");
		sqlStr.append("SELECT * FROM ( SELECT A.*, ROWNUM RN FROM(");
		sqlStr.append(sql)
				.append(") A WHERE ROWNUM <= ")
				.append((page.getPage() - 1) * page.getPageSize()
						+ page.getPageSize()).append(") WHERE RN >=")
				.append((page.getPage() - 1) * page.getPageSize() + 1);

		page.setCount(getRsCount(sql));
		return sqlStr.toString();
	}

	/**
	 * 获取所有结果条数
	 * 
	 * @param sql
	 * @return
	 */
	private int getRsCount(String sql) {
		int count = 0;
		StringBuffer sbr = new StringBuffer();
		sbr.append("SELECT COUNT(*) FROM (").append(sql).append(") t");
		count = jdbcTemplate.queryForObject(sbr.toString(), Integer.class);
		return count;
	}

	/**
	 * <p>
	 * 根据SQL语句查询数据
	 * </p>
	 * <br>
	 * By WenJing
	 * 
	 * @param sql
	 *            Sql语句<br>
	 */
	public List<Object[]> queryDataListByToExcel(StringBuffer sql)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		{
			System.out.print(sql.toString());
			return this.getJdbcTemplate().query(sql.toString(),
					new RowMapper<Object[]>() {
						public Object[] mapRow(ResultSet rs, int rowNum)
								throws SQLException {
							ResultSetMetaData rsmd = rs.getMetaData();
							int colsnum = rsmd.getColumnCount();
							Object[] obj = new Object[colsnum];
							for (int i = 0; i < colsnum; i++) {
								obj[i] = rs.getString(i + 1);
							}
							return obj;
						}
					});

		}
	}
	
	/**
	 * 根据sql和分页对象获取listMap
	 * 
	 * @param page
	 * @param sql
	 * @return
	 * @throws Exception List<Map<String,Object>>
	 *
	 * @Author taozhaocai
	 * @Date 2016-4-28 下午1:15:09 
	 * 修改日期  修改人  修改内容
	 */
	public List<Map<String, Object>> queryPageMapList2(PageListData page,String sql) throws Exception {
		String paginationSql = createPageSql(sql, page);
		return this.getJdbcTemplate().queryForList(paginationSql);
	}
	

}
